const db = require('./database')
const { executeDDL } = require('./database')

// 重新设计的数据库表结构
// 混合方案：用户表保留管理必需字段 + 独立业务表进行详细管理

const initDatabaseTables = async () => {
  try {
    console.log('🚀 开始初始化数据库表结构...')

    // 1. 用户主表（保留管理员需要的字段）
    await db.query(`
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        email VARCHAR(255) UNIQUE NOT NULL,
        username VARCHAR(100) NOT NULL,
        password_hash VARCHAR(255) NOT NULL,
        nickname VARCHAR(100) DEFAULT NULL,
        avatar_url VARCHAR(500) DEFAULT NULL,
        phone VARCHAR(20) DEFAULT NULL,
        gender ENUM('male', 'female', 'other') DEFAULT 'other',
        birth_date DATE DEFAULT NULL,
        bio TEXT DEFAULT NULL,
        
        -- 管理员需要的核心字段（保留在主表）
        role ENUM('user', 'admin', 'moderator') DEFAULT 'user',
        status ENUM('active', 'banned', 'inactive') DEFAULT 'active',
        vip_status ENUM('none', 'monthly', 'yearly') DEFAULT 'none',
        vip_expire_date DATETIME DEFAULT NULL,
        level INT DEFAULT 1,
        balance DECIMAL(10,2) DEFAULT 0.00,
        
        -- 冗余统计字段（便于管理查询）
        total_recharge DECIMAL(10,2) DEFAULT 0.00,
        total_consumption DECIMAL(10,2) DEFAULT 0.00,
        reading_time INT DEFAULT 0,
        last_login_at TIMESTAMP NULL,
        
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        
        INDEX idx_email (email),
        INDEX idx_username (username),
        INDEX idx_role (role),
        INDEX idx_status (status),
        INDEX idx_vip_status (vip_status),
        INDEX idx_level (level)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 2. VIP详细记录表（记录每次VIP购买历史）
    await db.query(`
      CREATE TABLE IF NOT EXISTS user_vip_records (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        vip_type ENUM('monthly', 'yearly') NOT NULL,
        start_date DATETIME NOT NULL,
        expire_date DATETIME NOT NULL,
        amount DECIMAL(10,2) NOT NULL,
        payment_method ENUM('alipay', 'wechat', 'bank', 'admin') DEFAULT 'admin',
        order_no VARCHAR(64) UNIQUE,
        status ENUM('active', 'expired', 'cancelled') DEFAULT 'active',
        created_by ENUM('user', 'admin') DEFAULT 'user',
        admin_reason TEXT DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_expire_date (expire_date),
        INDEX idx_status (status)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 3. 钱包交易记录表（详细的收支记录）
    await db.query(`
      CREATE TABLE IF NOT EXISTS wallet_transactions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        type ENUM('recharge', 'consumption', 'admin_adjust', 'refund') NOT NULL,
        amount DECIMAL(10,2) NOT NULL,
        balance_before DECIMAL(10,2) NOT NULL,
        balance_after DECIMAL(10,2) NOT NULL,
        description VARCHAR(255),
        related_type ENUM('chapter', 'vip', 'gift', 'manual') DEFAULT NULL,
        related_id INT DEFAULT NULL,
        order_no VARCHAR(64) DEFAULT NULL,
        created_by ENUM('user', 'admin') DEFAULT 'user',
        admin_reason TEXT DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_type (type),
        INDEX idx_created_at (created_at),
        INDEX idx_order_no (order_no)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 4. 小说表
    await db.query(`
      CREATE TABLE IF NOT EXISTS novels (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        author VARCHAR(100) NOT NULL,
        description TEXT,
        cover_url VARCHAR(500),
        category ENUM('fantasy', 'urban', 'romance', 'historical', 'scifi', 'mystery', 'military', 'game') NOT NULL,
        tags JSON,
        status ENUM('ongoing', 'completed', 'paused') DEFAULT 'ongoing',
        word_count INT DEFAULT 0,
        chapter_count INT DEFAULT 0,
        rating DECIMAL(3,2) DEFAULT 0.00,
        rating_count INT DEFAULT 0,
        view_count INT DEFAULT 0,
        favorite_count INT DEFAULT 0,
        is_vip BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_category (category),
        INDEX idx_status (status),
        INDEX idx_rating (rating),
        INDEX idx_view_count (view_count),
        INDEX idx_updated_at (updated_at),
        FULLTEXT idx_title_author (title, author)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 5. 章节表
    await db.query(`
      CREATE TABLE IF NOT EXISTS chapters (
        id INT AUTO_INCREMENT PRIMARY KEY,
        novel_id INT NOT NULL,
        title VARCHAR(255) NOT NULL,
        content LONGTEXT NOT NULL,
        chapter_number INT NOT NULL,
        word_count INT DEFAULT 0,
        is_vip BOOLEAN DEFAULT FALSE,
        price DECIMAL(5,2) DEFAULT 0.00,
        view_count INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (novel_id) REFERENCES novels(id) ON DELETE CASCADE,
        INDEX idx_novel_id (novel_id),
        INDEX idx_chapter_number (chapter_number),
        INDEX idx_is_vip (is_vip),
        UNIQUE KEY unique_novel_chapter (novel_id, chapter_number)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 6. 用户书架表
    await db.query(`
      CREATE TABLE IF NOT EXISTS user_bookshelf (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        novel_id INT NOT NULL,
        last_read_chapter_id INT DEFAULT NULL,
        reading_progress DECIMAL(5,2) DEFAULT 0.00,
        is_favorite BOOLEAN DEFAULT TRUE,
        added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        last_read_at TIMESTAMP NULL,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (novel_id) REFERENCES novels(id) ON DELETE CASCADE,
        FOREIGN KEY (last_read_chapter_id) REFERENCES chapters(id) ON DELETE SET NULL,
        UNIQUE KEY unique_user_novel (user_id, novel_id),
        INDEX idx_user_id (user_id),
        INDEX idx_novel_id (novel_id),
        INDEX idx_last_read_at (last_read_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 7. 阅读历史表
    await db.query(`
      CREATE TABLE IF NOT EXISTS reading_history (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        novel_id INT NOT NULL,
        chapter_id INT NOT NULL,
        reading_time INT DEFAULT 0,
        reading_progress DECIMAL(5,2) DEFAULT 0.00,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (novel_id) REFERENCES novels(id) ON DELETE CASCADE,
        FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_novel_id (novel_id),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 8. 章节购买记录表
    await db.query(`
      CREATE TABLE IF NOT EXISTS chapter_purchases (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        chapter_id INT NOT NULL,
        price DECIMAL(5,2) NOT NULL,
        purchased_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE CASCADE,
        UNIQUE KEY unique_user_chapter (user_id, chapter_id),
        INDEX idx_user_id (user_id),
        INDEX idx_chapter_id (chapter_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 9. 充值订单表
    await db.query(`
      CREATE TABLE IF NOT EXISTS recharge_orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        order_no VARCHAR(64) UNIQUE NOT NULL,
        amount DECIMAL(10,2) NOT NULL,
        coins DECIMAL(10,2) NOT NULL,
        payment_method ENUM('alipay', 'wechat', 'bank') NOT NULL,
        status ENUM('pending', 'success', 'failed', 'cancelled') DEFAULT 'pending',
        third_party_order_id VARCHAR(255),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        paid_at TIMESTAMP NULL,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_order_no (order_no),
        INDEX idx_status (status),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 10. VIP订单表
    await db.query(`
      CREATE TABLE IF NOT EXISTS vip_orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        order_no VARCHAR(64) UNIQUE NOT NULL,
        vip_type ENUM('monthly', 'yearly') NOT NULL,
        amount DECIMAL(10,2) NOT NULL,
        payment_method ENUM('alipay', 'wechat', 'bank') NOT NULL,
        status ENUM('pending', 'success', 'failed', 'cancelled') DEFAULT 'pending',
        start_date DATETIME,
        expire_date DATETIME,
        third_party_order_id VARCHAR(255),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        paid_at TIMESTAMP NULL,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_order_no (order_no),
        INDEX idx_status (status)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 11. 用户评论表
    await db.query(`
      CREATE TABLE IF NOT EXISTS comments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        novel_id INT NOT NULL,
        chapter_id INT DEFAULT NULL,
        content TEXT NOT NULL,
        rating INT CHECK (rating >= 1 AND rating <= 5),
        like_count INT DEFAULT 0,
        reply_to_id INT DEFAULT NULL,
        status ENUM('active', 'hidden', 'deleted') DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (novel_id) REFERENCES novels(id) ON DELETE CASCADE,
        FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE CASCADE,
        FOREIGN KEY (reply_to_id) REFERENCES comments(id) ON DELETE SET NULL,
        INDEX idx_novel_id (novel_id),
        INDEX idx_user_id (user_id),
        INDEX idx_created_at (created_at),
        INDEX idx_status (status)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 12. 管理日志表（记录管理员操作）
    await db.query(`
      CREATE TABLE IF NOT EXISTS admin_logs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        admin_id INT NOT NULL,
        action_type ENUM('user_edit', 'balance_adjust', 'vip_grant', 'user_ban', 'user_unban') NOT NULL,
        target_user_id INT NOT NULL,
        before_data JSON,
        after_data JSON,
        reason TEXT,
        ip_address VARCHAR(45),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (target_user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_admin_id (admin_id),
        INDEX idx_target_user_id (target_user_id),
        INDEX idx_action_type (action_type),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 13. 系统配置表
    await db.query(`
      CREATE TABLE IF NOT EXISTS system_configs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        config_key VARCHAR(100) UNIQUE NOT NULL,
        config_value TEXT,
        description VARCHAR(255),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_config_key (config_key)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 插入系统配置默认值
    await db.query(`
      INSERT IGNORE INTO system_configs (config_key, config_value, description) VALUES
      ('chapter_base_price', '5', '章节基础价格（书币）'),
      ('vip_monthly_price', '15.00', 'VIP月费价格（元）'),
      ('vip_yearly_price', '128.00', 'VIP年费价格（元）'),
      ('free_chapter_count', '5', '免费章节数量'),
      ('coin_exchange_rate', '100', '1元 = 100书币'),
      ('min_recharge_amount', '1.00', '最小充值金额（元）'),
      ('max_recharge_amount', '1000.00', '最大充值金额（元）')
    `)

    console.log('✅ 数据库表结构初始化完成')

    // 创建触发器：自动更新用户表的统计字段
    try {
      // 先删除已存在的触发器
      await executeDDL(
        'DROP TRIGGER IF EXISTS update_user_stats_on_transaction'
      )

      // 创建新触发器
      await executeDDL(`
        CREATE TRIGGER update_user_stats_on_transaction
        AFTER INSERT ON wallet_transactions
        FOR EACH ROW
        BEGIN
          UPDATE users SET 
            balance = NEW.balance_after,
            total_recharge = CASE WHEN NEW.type = 'recharge' THEN total_recharge + NEW.amount ELSE total_recharge END,
            total_consumption = CASE WHEN NEW.type = 'consumption' THEN total_consumption + NEW.amount ELSE total_consumption END
          WHERE id = NEW.user_id;
        END
      `)
    } catch (error) {
      console.warn('⚠️ 触发器创建警告:', error.message)
    }

    console.log('✅ 数据库触发器创建完成')
  } catch (error) {
    console.error('❌ 数据库表初始化失败:', error)
    throw error
  }
}

module.exports = {
  initDatabaseTables
}
